﻿SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductType](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_TipoDeProducto_Activo]  DEFAULT ((1)),
 CONSTRAINT [PK_TipoDeProducto] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Refrigerator]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Refrigerator](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Number] [nvarchar](100) NOT NULL,
	[SerialNumber] [nvarchar](100) NOT NULL,
	[Model] [nvarchar](100) NULL,
	[Capacity] [nvarchar](100) NULL,
	[Notes] [nvarchar](max) NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Refrigerator_Active]  DEFAULT ((1)),
 CONSTRAINT [PK_Refrigerador] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Route]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Route](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Ruta_Activo]  DEFAULT ((1)),
 CONSTRAINT [PK_Ruta] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BusinessType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BusinessType](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_TipoDeCliente_Activo]  DEFAULT ((1)),
 CONSTRAINT [PK_TipoDeCliente] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Product](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Weight] [decimal](4, 1) NULL,
	[ProductTypeId] [int] NOT NULL,
	[Price] [money] NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Product_Active]  DEFAULT ((1)),
 CONSTRAINT [PK_Producto] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sale]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Sale](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Date] [datetime] NOT NULL,
	[CustomerId] [int] NOT NULL,
	[ProductId] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Sale_Active]  DEFAULT ((1)),
 CONSTRAINT [PK_Venta] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RefrigeratorAssignment]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RefrigeratorAssignment](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BusinessId] [int] NOT NULL,
	[RefrigeratorId] [int] NOT NULL,
	[From] [datetime] NOT NULL,
	[To] [datetime] NULL,
 CONSTRAINT [PK_RefrigeradorConCliente] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Business]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Business](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Contract] [nvarchar](50) NULL,
	[BusinessTypeId] [int] NOT NULL,
	[RouteId] [int] NOT NULL,
	[Address] [nvarchar](255) NULL,
	[Address2] [nvarchar](255) NULL,
	[City] [nvarchar](100) NULL,
	[State] [nvarchar](50) NULL,
	[PostalCode] [nchar](10) NULL,
	[Phone] [nvarchar](50) NULL,
	[Phone2] [nvarchar](50) NULL,
	[Notes] [nvarchar](max) NULL,
	[ContractDate] [datetime] NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Cliente_Activo]  DEFAULT ((1)),
 CONSTRAINT [PK_Cliente_1] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Business] UNIQUE NONCLUSTERED 
(
	[Contract] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Producto_TipoDeProducto]') AND parent_object_id = OBJECT_ID(N'[dbo].[Product]'))
ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Producto_TipoDeProducto] FOREIGN KEY([ProductTypeId])
REFERENCES [dbo].[ProductType] ([Id])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Venta_Cliente]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sale]'))
ALTER TABLE [dbo].[Sale]  WITH CHECK ADD  CONSTRAINT [FK_Venta_Cliente] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Business] ([Id])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Venta_Producto]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sale]'))
ALTER TABLE [dbo].[Sale]  WITH CHECK ADD  CONSTRAINT [FK_Venta_Producto] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([Id])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_RefrigeradorConCliente_Cliente]') AND parent_object_id = OBJECT_ID(N'[dbo].[RefrigeratorAssignment]'))
ALTER TABLE [dbo].[RefrigeratorAssignment]  WITH CHECK ADD  CONSTRAINT [FK_RefrigeradorConCliente_Cliente] FOREIGN KEY([BusinessId])
REFERENCES [dbo].[Business] ([Id])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_RefrigeradorConCliente_Refrigerador]') AND parent_object_id = OBJECT_ID(N'[dbo].[RefrigeratorAssignment]'))
ALTER TABLE [dbo].[RefrigeratorAssignment]  WITH CHECK ADD  CONSTRAINT [FK_RefrigeradorConCliente_Refrigerador] FOREIGN KEY([RefrigeratorId])
REFERENCES [dbo].[Refrigerator] ([Id])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cliente_Ruta]') AND parent_object_id = OBJECT_ID(N'[dbo].[Business]'))
ALTER TABLE [dbo].[Business]  WITH CHECK ADD  CONSTRAINT [FK_Cliente_Ruta] FOREIGN KEY([RouteId])
REFERENCES [dbo].[Route] ([Id])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cliente_TipoDeCliente]') AND parent_object_id = OBJECT_ID(N'[dbo].[Business]'))
ALTER TABLE [dbo].[Business]  WITH CHECK ADD  CONSTRAINT [FK_Cliente_TipoDeCliente] FOREIGN KEY([BusinessTypeId])
REFERENCES [dbo].[BusinessType] ([Id])
